package com.hutao.medical.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hutao.medical.pojo.audit.AuditDetail;
import com.hutao.medical.pojo.audit.AuditErrorMsg;
import com.hutao.medical.pojo.vo.AuditingStandardVo;

/**
 * 生成excel工具类
 * 
 * @author 李玉涛
 *
 */
public class ExcelUtil {

	/**
	 * 导出excel
	 * 
	 * @param out
	 *            输出流
	 * @param title
	 *            标题
	 * @param bases
	 *            数据参数类型
	 * @param header
	 *            excel头信息
	 * @param mapList
	 *            excel数据信息
	 */
	public static void exportExcel(OutputStream out, String title, String[] bases, String[] header,
			List<Map<String, Object>> mapList) {
		// 创建一个工作薄
		HSSFWorkbook workbook = null;
		try {

			// 创建一个工作薄
			workbook = new HSSFWorkbook();
			// 创建一个sheet页
			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet页的列宽
			sheet.setDefaultColumnWidth(20);

			// 设置标题样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			// 设置字体居中显示
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 设置标题字体
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 25);
			titleStyle.setFont(titleFont);

			// 产生标题
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(title);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));

			// 表头样式
			HSSFCellStyle headStyle = workbook.createCellStyle();
			headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// 设置填充模式
			headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// 设置字体居中
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 设置字体样式
			HSSFFont headFont = workbook.createFont();
			headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
			headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
			headFont.setFontHeightInPoints((short) 16);
			headStyle.setFont(headFont);
			// 创建第二行，生成表格的表头行
			row = sheet.createRow(1);// 第二行

			for (int i = 0; i < header.length; i++) {
				cell = row.createCell(i);// 从第一列开始
				cell.setCellStyle(headStyle);// 设置列的样式
				cell.setCellValue(header[i]);
			}

			// set date format
			HSSFCellStyle dateCellStyle = workbook.createCellStyle();
			HSSFDataFormat format = workbook.createDataFormat();
			dateCellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
			int i = 0;

			for (Map<String, Object> cellMap : mapList) {
				row = sheet.createRow(2 + i);// 从第三行去写数据
				int j = 0;
				for (Map.Entry<String, Object> entry : cellMap.entrySet()) {
					cell = row.createCell(j);
					String base = bases[j];

					if (base.equals("Integer")) {
						if (null != entry.getValue()) {
							cell.setCellValue((Integer) entry.getValue());
						} else {
							cell.setCellValue("");
						}
					}

					if (base.equals("Date")) {
						if (null != entry.getValue()) {
							cell.setCellStyle(dateCellStyle);
							cell.setCellValue((Date) entry.getValue());
						} else {
							cell.setCellValue("");
						}

					}

					if (base.equals("String")) {
						if (null != entry.getValue()) {
							cell.setCellValue((String) entry.getValue());
						} else {
							cell.setCellValue("");
						}
					}

					if (base.equals("Double")) {
						if (null != entry.getValue()) {
							cell.setCellValue((Double) entry.getValue());
						} else {
							cell.setCellValue("");
						}
					}

					if (base.equals("Float")) {
						if (null != entry.getValue()) {
							cell.setCellValue((Float) entry.getValue());
						} else {
							cell.setCellValue("");
						}
					}

					if (base.equals("Long")) {
						if (null != entry.getValue()) {
							cell.setCellValue((Long) entry.getValue());
						} else {
							cell.setCellValue("");
						}
					}

					j++;
				}
				i++;
			}

			workbook.write(out);
			out.flush();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != out) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

			if (null != workbook) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * 导出excel数据
	 * 
	 * @param filePath
	 * @return
	 */
	public static void exportExcel(OutputStream out, String title, String[] header, List<Map<String, Object>> mapList) {
		// 创建一个工作薄
		HSSFWorkbook workbook = null;
		try {

			// 创建一个工作薄
			workbook = new HSSFWorkbook();
			// 创建一个sheet页
			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet页的列宽
			sheet.setDefaultColumnWidth(20);

			// 设置标题样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			// 设置字体居中显示
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 设置标题字体
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 25);
			titleStyle.setFont(titleFont);

			// 产生标题
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(title);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));

			// 表头样式
			HSSFCellStyle headStyle = workbook.createCellStyle();
			headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// 设置填充模式
			headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// 设置字体居中
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 设置字体样式
			HSSFFont headFont = workbook.createFont();
			headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
			headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
			headFont.setFontHeightInPoints((short) 16);
			headStyle.setFont(headFont);
			// 创建第二行，生成表格的表头行
			row = sheet.createRow(1);// 第二行
			for (int i = 0; i < header.length; i++) {
				cell = row.createCell(i);// 从第一列开始
				cell.setCellStyle(headStyle);// 设置列的样式
				cell.setCellValue(header[i]);
			}
			int i = 0;
			for (Map<String, Object> cellMap : mapList) {
				row = sheet.createRow(2 + i);// 从第三行去写数据
				int j = 0;
				for (Map.Entry<String, Object> entry : cellMap.entrySet()) {
					cell = row.createCell(j);
					Object obj = entry.getValue();
					if (obj instanceof Integer) {
						cell.setCellValue((Integer) entry.getValue());
					}
					if (obj instanceof Date) {
						cell.setCellValue(DateUtil.dateToStr((Date) entry.getValue()));
					}
					if (obj instanceof String) {
						cell.setCellValue((String) entry.getValue());
					}
					if (obj instanceof Double) {
						cell.setCellValue((Double) entry.getValue());
					}
					if (obj instanceof Float) {
						cell.setCellValue((Float) entry.getValue());
					}
					if (obj instanceof Long) {
						cell.setCellValue((Long) entry.getValue());
					}

					j++;
				}
				i++;
			}

			workbook.write(out);
			out.flush();
			out.close();
			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 导出审核信息excel
	 */
	public static void exportAuditExcel(OutputStream out, String title, String[] header,
			List<AuditDetail> auditDetailList) {

		// 创建一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个sheet页
		HSSFSheet sheet = workbook.createSheet();
		// 设置sheet页的列宽
		sheet.setDefaultColumnWidth(20);

		// 设置标题样式
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		// 设置字体居中显示
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置标题字体
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints((short) 25);
		titleStyle.setFont(titleFont);

		// 产生标题
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		cell.setCellStyle(titleStyle);
		cell.setCellValue(title);
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));

		// 表头样式
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		// 设置填充模式
		headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// 设置字体居中
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 设置字体样式
		HSSFFont headFont = workbook.createFont();
		headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
		headFont.setFontHeightInPoints((short) 16);
		headStyle.setFont(headFont);
		// 创建第二行，生成表格的表头行
		row = sheet.createRow(1);// 第二行

		for (int i = 0; i < header.length; i++) {
			cell = row.createCell(i);// 从第一列开始
			cell.setCellStyle(headStyle);// 设置列的样式
			cell.setCellValue(header[i]);
		}
		// 表头样式
		HSSFCellStyle contentStyle = workbook.createCellStyle();
		// 设置字体居中
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		int rowCount = 2;
		int regionCount = 2;
		for (int i = 0; i < auditDetailList.size(); i++) {
			AuditDetail audite = auditDetailList.get(i);
			List<AuditErrorMsg> auditErrorMsgList = audite.getAuditErrorMsgList();
			// 合并单元格
			row = sheet.createRow(rowCount);// 从第三行去写数据
			rowCount++;
			cell = row.createCell(0);// 第一列
			cell.setCellStyle(contentStyle);// 设置列的样式
			cell.setCellValue(audite.getPatientName());
			cell = row.createCell(1);
			cell.setCellStyle(contentStyle);// 设置列的样式
			cell.setCellValue(audite.getMrId());
			cell = row.createCell(2);
			cell.setCellStyle(contentStyle);// 设置列的样式
			cell.setCellValue(audite.getInHospitalTime());
			int length = auditErrorMsgList.size();
			for (int j = 0; j < auditErrorMsgList.size(); j++) {
				AuditErrorMsg auditErrorMsg5 = auditErrorMsgList.get(j);
				if (j == 0) {
					cell = row.createCell(3);
					cell.setCellStyle(contentStyle);// 设置列的样式
					cell.setCellValue(auditErrorMsg5.getErrorMsg());
					cell = row.createCell(4);
					cell.setCellStyle(contentStyle);// 设置列的样式
					cell.setCellValue(auditErrorMsg5.getErrorType());
				} else {

					row = sheet.createRow(rowCount);// 从第三行去写数据
					cell = row.createCell(3);
					cell.setCellStyle(contentStyle);// 设置列的样式
					cell.setCellValue(auditErrorMsg5.getErrorMsg());
					cell = row.createCell(4);
					cell.setCellStyle(contentStyle);// 设置列的样式
					cell.setCellValue(auditErrorMsg5.getErrorType());
					rowCount++;
				}
			}

			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(regionCount, regionCount + length - 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(regionCount, regionCount + length - 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(regionCount, regionCount + length - 1, 2, 2));
			regionCount += length;

		}
		try {
			workbook.write(out);
			out.flush();
			out.close();
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 读取后缀为xlsx的excel文件的数据
	 * 
	 * @param path
	 * @return List<StudentBean>
	 * @author zhang 2015-08-18 00:08
	 */
	public static List<?> readXlsx(String path) {

		XSSFWorkbook xssfWorkbook = null;
		try {
			InputStream is = new FileInputStream(path);
			xssfWorkbook = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		List<AuditingStandardVo> list = new ArrayList<AuditingStandardVo>();
		// MedRecordExamnation medRecordExamnation=null;
		if (xssfWorkbook != null) {
			// Read the Sheet
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				// Read the Row

				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						AuditingStandardVo auditingStandardVo = new AuditingStandardVo();
						// medRecordExamnation=new MedRecordExamnation();
						String name = getValue(xssfRow.getCell(0));
						String flag = getValue(xssfRow.getCell(1));
						auditingStandardVo.setName(name);
						auditingStandardVo.setFlag(flag);
						list.add(auditingStandardVo);

					}
				}
			}
		}
		System.out.println(list.size());
		return list;
	}

	/**
	 * 判断后缀为xlsx的excel文件的数据类型
	 * 
	 * @param xssfRow
	 * @return String
	 * @author zhang 2015-08-18 00:12
	 */
	@SuppressWarnings("static-access")
	private static String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}



}
